﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVTVK02Data : BaseFunction
    {
        
        public MVTVK02Data()
            : base()
        {

        }
        public MVTVK02Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWASTAMM(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select distinct b.vkkaufkrs||'-'||TO_NUMBER(b.vkkauflfd)||'-'||C.KDKUNDNA AS PI, "
                     + " trim(c.kdname1) as customer,C.KDLAND AS COUNTRY,C.KDORT AS PROVINCE, "
                     + " b.vkkneuben AS MERCHANDISER,b.vkkneudat AS ENTRYDATE,b.vkkanspzus AS ENDCUSTOMER, "
                     + " b.vkkwaehrsl AS CURRENCY,a.vkppostyp AS TYPE,a.vkpart AS ARTICLE,a.vkpfarbe AS COLOR, "
                     + " SUM(a.vkpmenge1*0.01)OVER(partition by b.vkkaufkrs,b.vkkauflfd,a.vkpart,a.vkpfarbe) as qty, "
                     + " a.vkpmeeh AS QTYUNIT,a.vkpliefterm AS DELIVERYDATE, "
                     + " min(a.vkppreis*0.001)over(partition by b.vkkaufkrs,b.vkkauflfd,a.vkpart,a.vkpfarbe) as price, "
                     + " a.vkppreh AS PRICEUNIT,trim(a.vkpart)||'-'||trim(a.vkpfarbe) as artcol, "
                     + " round(sum(a.vkpmenge1*a.vkpmeehfkt*a.vkppreis/a.vkpprehfkt*0.00001) "
                     + "  OVER(partition by b.vkkaufkrs,b.vkkauflfd,a.vkpart,a.vkpfarbe),2) as TOTVALUES "
                     + " from vkpos A,VKKOPF B,KDSTAMM C "
                     + " where A.vkpsa <>'AJ' AND A.VKPMANDANT = B.VKKMANDANT "
                     + " AND A.VKPSA = B.VKKSA AND A.VKPAUFKRS = B.VKKAUFKRS "
                     + " AND A.VKPAUFLFD = B.VKKAUFLFD AND A.VKPMANDANT = C.KDMANDANT "
                     + " AND A.VKPKUNDNR = C.KDKUNDNR and b.vkkaufart = 'NA' "
                     + " AND b.vkkneudat >= '" + FROMDATE + "' AND b.vkkneudat <= '" + ENDDATE + "' "
                     + " ORDER BY ENTRYDATE,PI "
                   ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["customer"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["customer"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PROVINCE"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PROVINCE"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ENDCUSTOMER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ENDCUSTOMER"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLOR"] = value;
                    }
                }
                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildMVTVK02Data(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWASTAMM(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}